<?php
	include("../connect/connect_dsccmis.php");
	$sql = "drop table if exists gis_victim;";
	$rs=mysql_query($sql);
    $sql="CREATE TABLE gis_victim AS SELECT rolplcidgen,t1.prov,t1.evnid,t4.evndte,t4.cdscc,t4.mis_evntypid,t1.losbody,mis_losbody,sex,regnme,perid,nme,surnme,t1.losbodynme,t3.vill_name,tam_name,amp_name,
prov_name,t3.lat,t3.lng,perupddte FROM 
 (SELECT (CASE WHEN LEFT(rolplcidgen,2) IN ('90','94','95','96') THEN CONCAT(rolplcidgen,rolvllnogen) ELSE CONCAT(LEFT(rolplcidgen,2),'000000') END)
 AS rolplcidgen,LEFT(rolplcidgen,2) AS prov,evnid,losbody,sex,regnme,perid,nme,surnme,losbodynme,
perupddte FROM dscc.viewlosofper) t1
 INNER JOIN convert_losbody t2 ON t1.losbody=t2.losbody LEFT JOIN convert_latlngofsouthprov t3
ON t1.rolplcidgen=t3.vill_code INNER JOIN gis_event t4 ON t1.evnid=t4.evnid";
// where concat(t1.rolplcidgen,t1.rolvllnogen) <> '00000000'
	$rs=mysql_query($sql);
	//mysql_close($c);
	echo "<br>complete create gis_victim on dsccmis";
?>